package com.the_ring.chapter07.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import androidx.annotation.Nullable;

import com.the_ring.chapter07.bean.BillInfo;


import java.util.ArrayList;
import java.util.List;

public class DatabaseHelper extends SQLiteOpenHelper {

    private static final String DB = "bill.db";
    private static final String TABLE = "bill";
    private static DatabaseHelper databaseHelper;
    private SQLiteDatabase database;

    private final String idColumn = "id";
    private final String dateColumn = "date";
    private final String yearColumn = "year";
    private final String monthColumn = "month";
    private final String typeColumn = "type";
    private final String detailColumn = "detail";
    private final String moneyColumn = "money";
    private final String[] columns = {idColumn, dateColumn, yearColumn, monthColumn, typeColumn, detailColumn, moneyColumn};

    public static DatabaseHelper getInstance(Context context) {
        if (databaseHelper == null) {
            databaseHelper = new DatabaseHelper(context, DB, null, 1);
        }
        return databaseHelper;
    }

    public DatabaseHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String sql = "create table if not exists " + TABLE + "(" +
                idColumn + " INTEGER PRIMARY KEY autoincrement," +
                dateColumn + " varchar," +
                yearColumn + " int, " +
                monthColumn + " int," +
                typeColumn + " int," +
                detailColumn + " text," +
                moneyColumn + " int);";
        db.execSQL(sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }

    public void openWriteLink() {
        if (database == null || !database.isOpen()) {
            database = getWritableDatabase();
        }
    }

    public void closeLink() {
        if (database != null && database.isOpen()) {
            database.close();
        }
    }

    public List<BillInfo> queryByMonth(int year, int month) {
        List<BillInfo> infoList = new ArrayList<>();
        Cursor cursor = database.query(TABLE, columns, yearColumn + "=? and " + monthColumn + "=?", new String[]{String.valueOf(year), String.valueOf(month)}, null, null, null);
        while (cursor.moveToNext()) {
            BillInfo info = new BillInfo();
            info.id = cursor.getInt(0);
            info.date = cursor.getString(1);
            info.year = cursor.getInt(2);
            info.month = cursor.getInt(3);
            info.type = cursor.getInt(4);
            info.detail = cursor.getString(5);
            info.money = cursor.getInt(6);
            infoList.add(info);
        }
        cursor.close();
        return infoList;
    }

    public BillInfo queryById(int id) {
        Cursor cursor = database.query(TABLE, columns, idColumn + "=?", new String[]{String.valueOf(id)}, null, null, null);
        BillInfo info = null;
        if (cursor.moveToFirst()) {
            info = new BillInfo();
            info.id = cursor.getInt(0);
            info.date = cursor.getString(1);
            info.year = cursor.getInt(2);
            info.month = cursor.getInt(3);
            info.type = cursor.getInt(4);
            info.detail = cursor.getString(5);
            info.money = cursor.getInt(6);
        }
        cursor.close();
        return info;
    }

    public void insert(BillInfo info) {
        ContentValues val = new ContentValues();
        val.put(dateColumn, info.date);
        val.put(yearColumn, info.year);
        val.put(monthColumn, info.month);
        val.put(typeColumn, info.type);
        val.put(detailColumn, info.detail);
        val.put(moneyColumn, info.money);
        database.insert(TABLE, null, val);
    }

    public void delete(int id) {
        database.delete(TABLE, String.format("id=%d", id), null);
    }

    public void update(BillInfo info) {
        ContentValues val = new ContentValues();
        val.put(dateColumn, info.date);
        val.put(yearColumn, info.year);
        val.put(monthColumn, info.month);
        val.put(typeColumn, info.type);
        val.put(detailColumn, info.detail);
        val.put(moneyColumn, info.money);
        database.update(TABLE, val, idColumn + "=" + info.id, null);
    }
}
